package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Event;

public class EventDB implements EventDBIF {
	
	private  Connection con;
    /** Creates a new instance of DBEmployee */
    public EventDB() {
      con = DbConnection1.getInstance().getDBcon();
    }


	public ArrayList<Event> getAllEvents(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	public Event findEvent(int eventId, boolean retrieveAssociation) {
		 String wClause = "  eventID = '" + eventId + "'";
	        return singleWhere(wClause);
	}
	

	public ArrayList<Event> searchEventName(String name, boolean retrieveAssociation) {
		String wClause = "Eventname like '%" + name + "%'";
        System.out.println("SearchEventName " + wClause);
        return miscWhere(wClause, retrieveAssociation);
	}

	public int insertEvent(Event ev) throws Exception {
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(eventID) from event");
        nextID = nextID + 1;
        System.out.println("next eventID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO Event(eventID, eventname, eventDate, eventstarttime, eventendtime, Eventdescription, eventpricetype)  VALUES('"+
	  		nextID + "','" +
			ev.getName()  + "','"  +
		    ev.getDate()  + "','"  +
		    ev.getStartTime()  + "','"  +
		    ev.getEndTime()  + "','"  +
		    ev.getDescription()  + "','"  +
            ev.getPriceType() + "')";
                     

       System.out.println("insert : " + query);
      try{ // insert new Event +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Event ikke oprettet");
          throw new Exception ("Event is not inserted correct");
       }
       return(rc);
	}

	public int updateEvent(Event ev) {
		Event evObj  = ev;
		int rc=-1;

		String query="UPDATE Event SET "+
		 	  "EventName ='"+ evObj.getName()+"', "+
		 	  "EventDate ='"+ evObj.getDate() + "', " +
              "EventStartTime ='"+ evObj.getStartTime() + "', " +
               "EventEndTime ='"+ evObj.getEndTime() + "', " +
               "EventDescription ='"+ evObj.getDescription() + "', " +
               "EventPriceType ='"+ evObj.getPriceType() + "' " +
		          " WHERE EventID = '"+ evObj.getEventId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update Event
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Event db: "+ex);
	  	}
		return(rc);
	}

	public int deleteEvent(int eventID) {
		int rc=-1;
		  
	  	String query="DELETE FROM Event WHERE eventID = '" +
				eventID + "'";
                System.out.println(query);
	  	try{ // delete from event
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in event db: "+ex);
   	        }
		return(rc);
	}
	
	private Event singleWhere(String wClause)
    {	
        ResultSet results;
        Event evObj = new Event();
        String query =  buildQuery(wClause);
        System.out.println("DbEvent -singleWhere " + query); 
        try{ // read from Event
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       evObj = buildEvent(results);  
           }//end if
	   stmt.close();
	}//slut try	
	catch(Exception e){
	   System.out.println("Query exception - select event : "+e);
	   e.printStackTrace();
        }
	return evObj;

    
    }
	
	 //miscWhere is used when more than one Event is selected and build
    private ArrayList<Event> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Event> list = new ArrayList<Event>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbEvent " + query); 
  		try{ // read from Event
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
		     	 Event evObj = new Event();
			 evObj = buildEvent(results);	
                         list.add(evObj);
                         //missing test on retriveAssociation
			}//end while
			stmt.close();
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select event : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Event buildEvent(ResultSet results)
    {
       Event evObj = new Event();
       
         try{
               evObj.setEventId(results.getInt("eventID"));
               evObj.setName(results.getString("eventname"));
               evObj.setDate(results.getString("eventdate"));
               evObj.setStartTime(results.getString("eventstarttime"));
               evObj.setEndTime(results.getString("eventendtime"));
               evObj.setDescription(results.getString("Eventdescription"));
               evObj.setPriceType(results.getString("eventpricetype"));
               
          }
         catch(Exception e)
         {
             System.out.println("building employee object");
         }
        
        return evObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT eventID, eventName, eventDate, eventStartTime, EventEndTime, EventDescription, eventPriceType FROM Event"; 
            
           

		if (wClause.length()>0)
			query = query + "  where " + wClause;
			
		return query;
	}



}
